﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Xml;
using ExcelDataReader;
using SdmxMl.SmsImportCfg;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;


namespace SdmxMl.Excel
{
    public class SozanLoader
    {
        private ExcelDataReader.ExcelDataReader spreadsheet = null;
        private ExcelDataReader.ExcelDataReader spreadsheetFr = null;
        private ExcelDataReader.ExcelDataReader spreadsheetIt = null;
        private string treePath;

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }

        public SozanLoader(string xlPath)
        {
            treePath = xlPath;

        }

        public void Import()
        {
            string tp = treePath;
            spreadsheet = LoadSources(treePath);
            spreadsheetFr = LoadSources(treePath.Replace("de", "fr"));
            spreadsheetIt = LoadSources(tp.Replace("de", "it"));
            // Establich dictionary
            DataTable dt = spreadsheet.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            WorkSheetHelper whFr = new WorkSheetHelper(spreadsheetFr.WorkbookData.Tables[0]);
            WorkSheetHelper whIt = new WorkSheetHelper(spreadsheetIt.WorkbookData.Tables[0]);

            // Create category artefact
            CategorySchemeType cats = new CategorySchemeType();
            cats.AgencyId = "CH1_SMS";
            cats.Id = "CAT_SOC_PREST";
            cats.Name.UpdateForLanguage("fr", "Inventaire des prestations sociales sous condition de ressources");
            cats.Name.UpdateForLanguage("de", "Inventar der bedarfsabhängigen Sozialleistungen");
            cats.Name.UpdateForLanguage("it", "Inventario delle prestazioni sociali legate ai bisogni");


            // Create categories hierarchy
            int[] levels = new int[3];
            levels[0] = -1; // as 1st item is introduction

            int curLevel = 0;
            Category previousCategory = null;
            CategoryList curList = cats.CategoryList;

            for (int row = 1; row <= dt.Rows.Count; row++)
            {

                // Col 3 4 5
                for (int i = 0; i < 3; ++i)
                {
                    string text = wh.GetCellContent(row, 3 + i);
                    if (text.Length > 0)
                    {
                        // Create category item
                        Category c = new Category(cats.CategoryList, null);
                        c.Id = (++levels[i]).ToString();
                        c.Name.UpdateForLanguage("en", text);
                        c.Name.UpdateForLanguage("de", text);
                        c.Name.UpdateForLanguage("fr", whFr.GetCellContent(row, 3 + i));
                        c.Name.UpdateForLanguage("it", whIt.GetCellContent(row, 3 + i));
                        // is same level
                        if (i == curLevel)
                        {
                            c.ParentCategory = previousCategory != null ? previousCategory.ParentCategory : null;

                            curList.Add(c);
                            previousCategory = c;
                            curLevel = i;
                            if (c.ParentCategory == null)
                            {
                                Annotation a = new Annotation("SMS_TYPE");
                                a.Text.UpdateDefaultText("P");
                                if (cats.CategoryList.Count == 1)
                                    a.Text.UpdateDefaultText("I");

                                c.AnnotationList.Add(a);
                            }
                        }
                        else if (i > curLevel)
                        {
                            curList = previousCategory.CategoryList;
                            c.ParentCategory = previousCategory;

                            curList.Add(c);
                            previousCategory = c;
                            curLevel = i;
                        }
                        else
                        {
                            Category ParentCat = previousCategory.ParentCategory != null ? previousCategory.ParentCategory.ParentCategory : null;
                            curList = ParentCat != null  ? ParentCat.CategoryList : cats.CategoryList;
                            c.ParentCategory = ParentCat;

                            curList.Add(c);
                            previousCategory = c;
                            curLevel = i;
                            if (c.ParentCategory == null)
                            {
                                Annotation a = new Annotation("SMS_TYPE");
                                a.Text.UpdateDefaultText("P");
                                c.AnnotationList.Add(a);
                            }

                        }
                    }
                }

            }

            SmManager sm = new SmManager();
            
            // Now concepts
            ImportConcepts(sm);

            
            
            
            
                    sm.PutArtefact(SdmxArtefactType.CategorySchemes, cats);
                    object ob = NavigationManager.LoadFromSmManager(sm);
                    NavigationManager.RequestNavigation(ob);

        }


        private void ImportConcepts(SmManager sm)
        {
            ConceptSchemeType cst = new ConceptSchemeType();
            cst.AgencyId = "CH1_SMS";
            cst.Id = "CS_SOZAN";
            sm.PutArtefact(SdmxArtefactType.Concepts, cst);

            using (StreamReader sr = new StreamReader(@"C:\doc\sozin\concepts_de.txt", Encoding.Default))
            {
                string line = sr.ReadLine();
                while (line != null)
                {
                    line = line.Trim();
                    int ndx = line.IndexOf(' ');
                    string id = line.Substring(0, ndx).Replace(".", "_");
                    string txt = line.Substring(ndx + 1);

                    ConceptType c = new ConceptType();
                    c.Id = id;
                    c.Name.UpdateForLanguage("de", txt);
                    cst.ConceptList.Add(c);
                    line = sr.ReadLine();
                }
            }
            using (StreamReader sr = new StreamReader(@"C:\doc\sozin\concepts_fr.txt", Encoding.Default))
            {
                string line = sr.ReadLine();
                while (line != null)
                {
                    line = line.Trim();
                    int ndx = line.IndexOf(' ');
                    string id = line.Substring(0, ndx).Replace(".", "_");
                    string txt = line.Substring(ndx + 1);

                    ConceptType c = cst.ConceptList.LocateItem(id) as ConceptType;
                    
                    c.Name.UpdateForLanguage("fr", txt);
                    line = sr.ReadLine();
                }
            }
        }
            
                


    }
}
